/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package mapping;
import java.sql.ResultSet;
import mapping.SqlLib;
import entreprise.Service;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Quoc-Khai
 */
public class DBService {
    public Service getService(String serviceID) throws SQLException{
        Service iService = new Service();
        String selectQuery;
        selectQuery = "SELECT * FROM service WHERE serviceID="+serviceID;
        
        SqlLib connec = new SqlLib();
        if (connec.initConnection()) {
            ResultSet sqlResultSet = connec.getDataFromQuery(selectQuery);
            
            while(sqlResultSet.next()) {
               iService.setServiceID(sqlResultSet.getString("serviceID")); 
               iService.setServiceName(sqlResultSet.getString("serviceName")); 
               iService.setServicePrice(sqlResultSet.getString("servicePrice")); 
               iService.setServiceDescription(sqlResultSet.getString("serviceDescription"));   
            }
        } else {
//            System.out.println("Service not found!");
        } 
        return iService;
    }
    public boolean createService(Service service){
        //TODO;
         String insertQuery;
        insertQuery = "INSERT INTO service (serviceName, servicePrice, serviceDescription) VALUES ('" + service.getServiceName()+ "','" + service.getServicePrice()+ "','" + service.getServiceDescription() + "')";
        SqlLib connec = new SqlLib();
        
        if (connec.initConnection()) {
            connec.execQuery(insertQuery);
            System.out.println("Insert Service success!");
            return true;
        }
        return false;
    }
    public boolean updateService(Service service){
        //TODO
        String updateQuery;
        updateQuery = "UPDATE service SET serviceName='" + service.getServiceName()+ "', servicePrice=" + service.getServicePrice()
                +",serviceDescription='" + service.getServiceDescription()+ "' WHERE serviceID="+service.getServiceID();
        
        SqlLib connec = new SqlLib();
        
        if (connec.initConnection()) {
            connec.execQuery(updateQuery);
            System.out.println("Update Service success!");
            return true;
        } 
        return false;
    }
    public boolean deleteService(int serviceID){
        //TODO
        String deleteQuery;
        deleteQuery = "DELETE FROM service WHERE serviceID="+serviceID; 
        SqlLib connec = new SqlLib();
        
        if (connec.initConnection()) {
            connec.execQuery(deleteQuery);
            System.out.println("Delete Service success!");
            return true;
        }
        return false;
    }    
    public ArrayList<Service> getServices(String serviceName,int from, int numberOfRow){
        ArrayList<Service> services=new ArrayList<>();
        String selectQuery="";
        if(serviceName.equals("")){
            selectQuery="select * from service LIMIT "+from+","+numberOfRow;
        }else{
            selectQuery="select * from service where serviceName LIKE '%"+serviceName+"%' LIMIT "+from+","+numberOfRow;
        }        
        SqlLib connec = new SqlLib();
        if (connec.initConnection()) {
            ResultSet sqlResultSet = connec.getDataFromQuery(selectQuery);            
            try {
                while(sqlResultSet.next()) {
                    Service service=new Service();
                        service.setServiceID(sqlResultSet.getString("serviceID"));
                        service.setServiceName(sqlResultSet.getString("serviceName"));
                        service.setServicePrice(sqlResultSet.getString("servicePrice"));
                        service.setServiceDescription(sqlResultSet.getString("serviceDescription"));
                    services.add(service);
                }
            } catch (SQLException ex) {
                Logger.getLogger(DBDrug.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else {
//            System.out.println("Drug not found!");
        }                
        return services;
    }    

    public int getNumberOfServices(String serviceSearchName) {
        String selectQuery="";
        if(serviceSearchName.equals("")){
            selectQuery="select count(*) as RECORDCOUNT from service";
        }else{
            selectQuery="select count(*) as RECORDCOUNT from service where serviceName LIKE '%"+serviceSearchName+"%'";
        }
        SqlLib connec = new SqlLib();
        if (connec.initConnection()) {
            ResultSet sqlResultSet = connec.getDataFromQuery(selectQuery);            
            try {
                connec.closeConnection();
                int numberOfRow=0;
                while(sqlResultSet.next()) {
                    numberOfRow=sqlResultSet.getInt("RECORDCOUNT");
                }
                return numberOfRow;
            } catch (SQLException ex) {
                Logger.getLogger(DBService.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else {
//            System.out.println("Drug not found!");
            return 0;
        } 
        return 0;
    }
}
